import pymysql
import mariadb
import random


class MyMarialdb1:
    def __init__(self,name,score):
        self.name = name
        self.score = score
        print(self.name)
        print(self.score)


#-----------------------------------------------------
class MyMarialdb:
    databaseinformation = ""
    host = ""
    user = ""
    password = ""
    prot = ""
    database = ""

    charuduoxuanti = "INSERT INTO duoxuanti (id,timu, daanA, daanB, daanC, daanD,daan,nandu,fenlei) VALUES (%d,%s,%s,%s,%s, %s,%s,%d,%d)"
    charudanxuanti = "INSERT INTO danxuanti (id,timu, daanA, daanB, daanC, daanD,daan,nandu,fenlei) VALUES (%d,%s,%s,%s,%s, %s,%s,%d,%d)"
    chahrupanduanti = "INSERT INTO panduanti (id,timu, daan, nandu,fenlei) VALUES (%d,%s,%s,%d,%d)"
    
    def __init__(self,host,port,user,password,database):
        self.host = host
        self.user = user
        self.password = password
        self.port = port
        self.database = database
        print(f"初始化成功  输入参数是这个   {self.host} |{self.user} |{self.password} |{self.port} |{self.database}")

    def setHost(self,host):
        self.host = host

    def setUser(self,user):
        self.user = user
    
    def setPassword(self, password):
        self.password = password

    def setPort(self,port):
        self.port = port

    def setDatabase(self,database):
        self.database = database
        
    #获得数据表所有数据
    def getALL(self,datatable):        
        #创建游标
        conn = mariadb.connect(user = self.user,
                               password = self.password,
                               host = self.host,
                               port = self.port,
                               database = self.database)
        cur  = conn.cursor()
        #执行查询语句
        sql = f"select * from  {datatable}"
        print(sql)
        #sql = "select count(id) from xuanzeti;"
        cur.execute(sql)
        #获取查询结果
        result = cur.fetchall()
        #打印结果
        for one in result:
            print(one)
    #获得数据表长度
    def getLenght(self,datatable):
            #创建游标
        conn = mariadb.connect(user = self.user,
                            password = self.password,
                            host = self.host,
                            port = self.port,
                            database = self.database)
        cur  = conn.cursor()
        try:
            count_sql = f"select count(id) from {datatable};"
            cur.execute(count_sql)
            count = cur.fetchall()
        #获得数据表数据长度
            print (count[0][0])
            return int(count[0][0])
        except:
            return -1
    #插入数据到数据表 
    #参数内容：  数据表名称，ID号，题目，选择A，选择B,选择C，选择D，答案，备份
    def insertDatatable(self,datatable,id1,subject,chooseA, choosB, choosC, choosD,choose,backup):
        conn = mariadb.connect(user = self.user,
                            password = self.password,
                            host = self.host,
                            port = self.port,
                            database = self.database)
        cur  = conn.cursor()
        s = f"INSERT INTO {datatable} (id,subject, chooseA, choosB, choosC, choosD,choose,backup)"
        #insert_query = "INSERT INTO xuanzeti (id,subject, chooseA, choosB, choosC, choosD,choose,backup) VALUES (%d,%s,%s,%s,%s, %s,%s,%s);"
        insert_query = s + " VALUES (%d,%s,%s,%s,%s, %s,%s,%s);"
    
        print(type(id1))
        values = (id1, subject,chooseA, choosB, choosC, choosD,choose,backup)
        cur.execute(insert_query, values)
        conn.commit()

    def insertDanxuanDatatable(self,datatable,id1, timu, daanA, daanB, daanC, daanD,daan,nandu,fenlei):
        conn = mariadb.connect(user = self.user,
                            password = self.password,
                            host = self.host,
                            port = self.port,
                            database = self.database)
        cur  = conn.cursor()
        s = f"INSERT INTO {datatable} (id,timu, daanA, daanB, daanC, daanD,daan,nandu,fenlei)"
        #insert_query = "INSERT INTO xuanzeti (id,timu, daanA, daanB, daanC, daanD,daan,nandu,fenlei) VALUES (%d,%s,%s,%s,%s, %s,%s,%d,%s);"
        insert_query = s + " VALUES (%d,%s,%s,%s,%s, %s,%s,%d,%d);"
    
        print(type(id1))
        values = (id1, timu, daanA, daanB, daanC, daanD,daan,nandu,fenlei)
        cur.execute(insert_query, values)
        conn.commit()

        
    def getTixing(self,datatable):
        conn = mariadb.connect(user = self.user,
                            password = self.password,
                            host = self.host,
                            port = self.port,
                            database = self.database)
        cur  = conn.cursor()
        out= "查找成功"
        try:
            sql = f"select * from  shujubiao"
            cur.execute(sql)
            #获取查询结果
            result = cur.fetchall()
            #打印结果
            for one in result:
                print(one)
        except:
            out = "查找错误"
        return out
    #内部函数：获得数据表插入结构
    def _getJiegou(self,count):
        print("函数： 获得题型结构")
        conn = mariadb.connect(user = self.user,
                            password = self.password,
                            host = self.host,
                            port = self.port,
                            database = self.database)
        cur  = conn.cursor()
        out= "查找成功"
        try:
            sql = f"select * from  jiegou"
            cur.execute(sql)
            #获取查询结果
            result = cur.fetchall()
            #打印结果
            for one in result:
                if one[0] == count:
                    out = one[1]
                    print("准备退出")
                    break
        except:
            out = "查找错误：没有您查找的结构"
        return out
    #内部函数：获得数据包结构类型编号
    def _getJiegouNumber(self,tablename):
        print("函数： 获得题型ID")
        conn = mariadb.connect(user = self.user,
                            password = self.password,
                            host = self.host,
                            port = self.port,
                            database = self.database)
        cur  = conn.cursor()
        out= "查找成功"
        try:
            sql = f'SELECT * FROM shujubiao WHERE shujubiao = \'{tablename}\''
            #sql = f"select  from  shujubiao"
            print(sql)
            cur.execute(sql)
            #获取查询结果
            result = cur.fetchall()
            out = 0
            print(result)
            print(type(result[0][1]))
            out = result[0][1]
        except:
            out = -1
        return out
    #输入数据表名称返回数据表插入结构
    def getJiegou(self,tablename):
        return self._getJiegou(self._getJiegouNumber("duanxuanti"))
    
    #插入单选题目
    def CharuDanxuanti(self, timu, daanA, daanB, daanC, daanD,daan,nandu,fenlei):
        lenght = self.getLenght("danxuanti")
        conn = mariadb.connect(user = self.user,
                            password = self.password,
                            host = self.host,
                            port = self.port,
                            database = self.database)
        cur  = conn.cursor()
        insert_query = self.charudanxuanti
        values = ( lenght+1, timu, daanA, daanB, daanC, daanD,daan,nandu,fenlei )
        cur.execute(insert_query, values)
        conn.commit()
    #插入多选题目
    def CharuDuoxuanti(self, timu, daanA, daanB, daanC, daanD,daan,nandu,fenlei):
        lenght = self.getLenght("duoxuanti")
        conn = mariadb.connect(user = self.user,
                            password = self.password,
                            host = self.host,
                            port = self.port,
                            database = self.database)
        cur  = conn.cursor()
        insert_query = self.charuduoxuanti
        values = (lenght+1, timu, daanA, daanB, daanC, daanD,daan,nandu,fenlei )
        cur.execute(insert_query, values)
        conn.commit()
    #插入判断题目 
    def CharuPanduanti(self, timu, daan,nandu,fenlei):
        lenght = self.getLenght("panduanti")
        conn = mariadb.connect(user = self.user,
                            password = self.password,
                            host = self.host,
                            port = self.port,
                            database = self.database)
        cur  = conn.cursor()
        insert_query = self.chahrupanduanti
        values = (lenght+1, timu, daan,nandu,fenlei )
        cur.execute(insert_query, values)
        conn.commit()
    #根据id号返回多选题题目
    def GetDuoxuanti(self,id):
        #print("函数： 获得题型ID")
        conn = mariadb.connect(user = self.user,
                            password = self.password,
                            host = self.host,
                            port = self.port,
                            database = self.database)
        cur  = conn.cursor()
        out= ""
        try:
            sql = f"SELECT * FROM duoxuanti WHERE id = {id}"
            cur.execute(sql)
            #获取查询结果
            result = cur.fetchall()
            out = 0
            out = result
        except:
            out = -1
        return out
    #根据id号返回单选题题目
    def GetDanxuanti(self,id):
        #print("函数： 获得题型ID")
        conn = mariadb.connect(user = self.user,
                            password = self.password,
                            host = self.host,
                            port = self.port,
                            database = self.database)
        cur  = conn.cursor()
        out= ""
        try:
            sql = f"SELECT * FROM danxuanti WHERE id = {id}"
            cur.execute(sql)
            #获取查询结果
            result = cur.fetchall()
            out = 0
            out = result
        except:
            out = -1
        return out
    #根据id号返回判断题题目
    def GetPanduanti(self,id):
        #print("函数： 获得题型ID")
        conn = mariadb.connect(user = self.user,
                            password = self.password,
                            host = self.host,
                            port = self.port,
                            database = self.database)
        cur  = conn.cursor()
        out= ""
        try:
            sql = f"SELECT * FROM panduanti WHERE id = {id}"
            cur.execute(sql)
            #获取查询结果
            result = cur.fetchall()
            out = 0
            out = result
        except:
            print("获得判断题目是出现错误")
            out = -1
        return out    


    def GetTextNandu(self,datatable,down,up):
        #创建游标
        conn = mariadb.connect(user = self.user,
                            password = self.password,
                            host = self.host,
                            port = self.port,
                            database = self.database)
        cur  = conn.cursor()
        try:
            #between A and B
            sql = f"SELECT * FROM {datatable} WHERE nandu between {down} and {up}"
            cur.execute(sql)
            count = cur.fetchall()

            #获得数据表数据长度
            for d in count:
                #print (d)
                pass
            return count
        except:
            print("查找错误")
            return -1
        

    def GetFenLei(self,datatable,fenlei):
        #创建游标
        conn = mariadb.connect(user = self.user,
                            password = self.password,
                            host = self.host,
                            port = self.port,
                            database = self.database)
        cur  = conn.cursor()
        try:
            #between A and B
            sql = f"SELECT id FROM {datatable} WHERE fenlei = {fenlei}"
            cur.execute(sql)
            count = cur.fetchall()

            #获得数据表数据长度
            for d in count:
                #print (d)
                pass
            return count
        except:
            print("查找错误")
            return -1
